package com.learn.db;

import java.sql.*;

/**
 * 功能：使用 JDBC 连接 MySQL 数据库
 *
 * @author wsh
 * @date 2021/1/21
 */
public class JdbcTest {
    /**
     * 数据库驱动、url、账号、密码
     */
    private static String driverClass = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false";
    private static String username = "root";
    private static String password = "root";

    /**
     * 功能：得到数据库连接
     *
     * @return
     * @author wsh
     * @date 2021/1/21
     */
    private static Connection getConnection() {
        try {
            // 加载数据库驱动类
            Class.forName(driverClass);
            // 获取数据库连接对象
            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接获取成功...");
            return conn;
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("数据库连接获取失败...");
            e.printStackTrace();
        } finally {
        }

        return null;
    }

    private static void insert(Connection connection) {
        String sql = "insert into t_sys_user(c_account_name, c_password, n_sex) values (?, ?, ?)";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "admin");
            ps.setString(2, "123456");
            ps.setInt(3, 1);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void select(Connection connection) {
        String sql = "select * from t_sys_user limit 0, ?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, 10);
            ResultSet r = ps.executeQuery();
            while (r.next()) {
                System.out.print(r.getInt(1) + "  ");
                System.out.print(r.getString(2) + "  ");
                System.out.print(r.getString(3) + "  ");
                System.out.println(r.getInt(4));
            }
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void update(Connection connection) {
        String sql = "update t_sys_user set c_account_name=?,n_sex=? where n_id=?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "admin001");
            ps.setInt(2, 0);
            ps.setInt(3, 2);
            int n = ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void delete(Connection connection) {
        String sql = "delete from t_sys_user where n_id=?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, 3);
            int n = ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Connection connection = getConnection();
        // 测试新增
//        insert(connection);
        // 测试修改
//        update(connection);
        // 测试删除
//        delete(connection);
        // 测试查询
        select(connection);
        // 关闭数据库连接
        try {
        } finally {
            if (null != connection) {
                try {
                    connection.close();
                    System.out.println("数据库连接关闭成功.");
                } catch (SQLException e) {
                    System.out.println("数据库连接关闭失败...");
                    e.printStackTrace();
                }
            }
        }
    }
}
